package com.sinai.mshab.server;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.sinai.mshab.client.services.Service;
import com.sinai.mshab.client.services.ServiceAsync;
import com.sinai.mshab.client.util.Answer;
import com.sinai.mshab.client.util.AnswerCollection;
import com.sinai.mshab.client.util.FieldName;
import com.sinai.mshab.client.util.User;
import com.sinai.mshab.client.util.WhereClause;
import com.sinai.mshab.client.ui.Key;

/**
 * This is the server-side implementation of the methods defined in the Services
 * class.
 * 
 * Essentially the purpose of this class is to communicate with the database, to
 * either retreive or store data depending on the requeset from the client side.
 * 
 * @author serge
 * 
 */
public class Server extends RemoteServiceServlet implements Service {

	/**
	 * The connection used to the database.
	 */
	protected static Connection conn;

	/**
	 * Boolean to keep track of whether any updates took place.
	 */
	protected boolean had_an_update;

	/**
	 * boolean to keep track if the server has been initialized or not
	 */
	protected boolean initialized = false;

	/**
	 * This arraylist will contain strings of table names that never should be
	 * inserted into.
	 */
	protected ArrayList neverInsertTables = new ArrayList();

	/**
	 * This arraylist will contain strings of table names that never should be
	 * updated with sql UPDATE command.
	 */
	protected ArrayList neverUpdateTables = new ArrayList();

	/**
	 * User defined server set up.
	 */
	public void initialize() {
		System.out.println("DEFAULT SERVER INIT.");
		initialized = true;
	}

	/**
	 * Establishes a connection with the database Might be a good idea to over
	 * ride this method in a specific sever class.
	 */
	public void getConnection() {

		initialize();

		conn = null;
		String url = "jdbc:mysql://server:port/";
		String dbName = "DatabaseName";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "username";
		String password = "password";
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager
					.getConnection(url + dbName, userName, password);
			System.out.println("Connected to the database");
		} catch (Exception e) {
			System.out.println("Connecting to the DB failed.");
			System.out.println(e.toString());
		}
	}

	/**
	 * Closes the connection to the database.
	 */
	public void closeConnection() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Returns the connection to the database created by the server.
	 * @return the connection to the database created by the server.
	 */
	public static Connection getConn() {
		return conn;
	}

	/**
	 * Retrieves the tables from the database.
	 * 
	 * @return an arraylist containing strings with the names of the available
	 *         tables in the database.
	 */
	public ArrayList getTables() {

		System.out.println("Server sending out table names");

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		ArrayList table_names = new ArrayList();

		try {
			// going through all the tables in the database.
			ResultSet tables = conn.createStatement().executeQuery(
					"show tables");
			while (tables.next()) {
				table_names.add(tables.getString(1));
			}
		} catch (Exception e) {
		}
		return table_names;
	}

	/**
	 * Retrieves the tables from the database.
	 * 
	 * @return an arraylist containing strings with the names of the available
	 *         tables in the database.
	 * 
	 * @gwt.typeArgs <java.lang.String, java.util.ArrayList>
	 */
	public HashMap getColumnNames() {

		System.out.println("Server sending out column names");

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		HashMap columns = new HashMap();

		try {
			// going through all the tables in the database.
			ResultSet tables = conn.createStatement().executeQuery(
					"show tables");
			while (tables.next()) {
				String table_name = tables.getString(1);

				ResultSet col_rs = conn.createStatement().executeQuery(
						"show columns from " + table_name);

				columns.put(table_name, new ArrayList());

				// adding all the columns to the current table name.
				while (col_rs.next()) {
					String column_name = col_rs.getString(1);
					String field_type = col_rs.getString(2);
					ArrayList list = (ArrayList) columns.get(table_name);
					FieldName field = new FieldName();
					field.setName(column_name);
					field.setTable(table_name);
					field.setType(field_type);
					list.add(field);
				}

			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return columns;
	}

	/**
	 * Checks that the key exists in the database.
	 * 
	 * @param k
	 *            the key to be checked
	 * @return true if key exists, false otherwise.
	 */
	public boolean validKey(Key k) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		try {
			// construct the sql check
			String sql = "SELECT * FROM " + k.getTableName() + " WHERE ";

			Iterator it = k.getKeys().keySet().iterator();
			int count = 0;
			while (it.hasNext()) {
				String column = (String) it.next();
				String val = (String) k.getKeys().get(column);

				if (count == 0) {
					sql += " " + column + "='" + val + "' ";
				} else {
					sql += " AND " + column + "='" + val + "' ";
				}
				count++;
			}

			// going through all the tables in the database.
			System.out.println("VALIDITY CHECK OF: " + sql);
			ResultSet success = conn.createStatement().executeQuery(sql);

			if (success.next()) {
				return true;
			}
		} catch (Exception e) {

		}

		return false;
	}

	/**
	 * Executes a custom sql statement. Returns a 2d array, represented by
	 * arraylist of arraylists Does not return everything, but a range. very
	 * useful for populating Table.
	 * 
	 * @param sql
	 *            the sql statement to execute.
	 * @param start
	 *            the first record to return
	 * @param end
	 *            the last record to return
	 * @return arraylist of arraylists, a 2d array with the information the
	 *         first arraylist contains an integer with the total record count.
	 */
	public ArrayList execteSQL(String sql, int start, int end) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		ArrayList result = new ArrayList();
		int current_record_number = 0;

		try {
			// going through all the tables in the database.
			ResultSet data = conn.createStatement().executeQuery(sql);
			ResultSetMetaData meta = data.getMetaData();

			ArrayList answer = new ArrayList();
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				answer.add(meta.getColumnName(i));
			}
			result.add(answer);

			// walking through the result.
			while (data.next()) {

				// update record count.
				current_record_number++;

				// Only store the requested records, skiping past uneeded
				// records
				if (current_record_number >= start
						&& current_record_number < end) {
					answer = new ArrayList();

					for (int i = 1; i <= meta.getColumnCount(); i++) {
						answer.add(data.getString(i));
					}
					result.add(answer);
				}
			}
		} catch (Exception e) {
			System.out.println("FAILED TO EXECUTE " + sql);
		}

		// appending the total number of records.
		result.add(0, new Integer(current_record_number));

		return result;
	}

	/**
	 * Executes the provided insert or update statement. First executes the
	 * update, if no rows were affected executes the insert sql instead.
	 * 
	 * @param insert_sql
	 *            the sql statement to insert information
	 * @param update_sql
	 *            the sql statement to update informtaion
	 */
	protected void executeSQL(String update_sql, String insert_sql) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		Statement stm;
		int rows_affected = 0;

		// attempting to execute the sql
		try {
			if (update_sql != null && update_sql.trim().length() > 0) {
				stm = conn.createStatement();
				rows_affected = stm.executeUpdate(update_sql);

				if (rows_affected > 0) {
					had_an_update = true;
					System.out.println("SUCCESSFULLY EXECUTED: " + update_sql);
				}
				stm.close();
			} else {
				System.out.println("FAILED: Badly formatted update sql: "
						+ update_sql);
			}
		} catch (Exception e) {
			System.out.println("FAILED TO EXECUTE: " + update_sql);
			System.out.println(e.toString());
		}

		// if no rows were affected, insert the sql
		try {
			if (insert_sql != null && insert_sql.trim().length() > 0) {
				stm = conn.createStatement();
				if (rows_affected == 0) {
					rows_affected = stm.executeUpdate(insert_sql);
					System.out.println("SUCCESSFULLY EXECUTED: " + insert_sql);
				}
				stm.close();
			} else {
				System.out.println("FAILED: Badly formatted INSERT sql: "
						+ insert_sql);
			}
		} catch (Exception e) {
			System.out.println("FAILED TO EXECUTE: " + insert_sql);
			System.out.println(e.toString());
		}
	}

	/**
	 * Method takes in an array of answer collections and a key. Places the data
	 * stored in the answer collections with the given primary key to the
	 * database.
	 * 
	 * @param a
	 *            the list of answer collections
	 * @param dbEntryKey
	 *            the primary key associated with the data.
	 */
	public void sendAnswer(AnswerCollection a, Key dbEntryKey) {
		AnswerCollection current_collection;
		Answer current_answer;
		String table_name = "";
		String insert_sql = "";
		String update_sql = "";
		String columns = "";
		String values = "";

		had_an_update = false;

		current_collection = a;

		// going throgh answers of collection
		for (int i = 0; i < current_collection.size(); i++) {
			current_answer = current_collection.get(i);

			// setting the table name
			table_name = current_answer.getTableName();

			// if the table name is null, move on to next answer
			if (table_name == null || table_name.trim().length() == 0) {
				continue;
			}

			HashMap fields_map = current_answer.getHashMap();
			Iterator keys = fields_map.keySet().iterator();

			columns = "";
			values = "";

			// if the collection is non-mergable, handle this collection
			// sperately
			ArrayList wheres = new ArrayList();
			String where_insert_cols = "";
			String where_insert_vals = "";
			String where_update = "";
			WhereClause w;

			if (!current_answer.isMergeable()) {
				wheres = current_answer.getWhereClauses();

				// going through the where clauses
				for (int k = 0; k < wheres.size(); k++) {
					w = (WhereClause) wheres.get(k);
					where_insert_cols = SQLUtils.appendColumn(
							where_insert_cols, w.getColumn());
					where_insert_vals = SQLUtils.appendValue(where_insert_vals,
							w.getValue());
					where_update = SQLUtils.appendWhere(where_update, w
							.getColumn(), w.getValue());
				}
			}

			// going through the keys of the answer
			while (keys.hasNext()) {
				String key = (String) keys.next();
				String value = (String) fields_map.get(key);

				// building up the columns and values
				if (!key.startsWith("ignore")
						&& !dbEntryKey.getKeys().containsKey(key)
						&& value.length() > 0) {
					columns = SQLUtils.appendColumn(columns, key);
					values = SQLUtils.appendValue(values, value);
				}
			}

			// making the insert sql
			if (columns.length() > 0) {
				update_sql = "";
				insert_sql = "";
				if (!neverUpdateTables.contains(table_name)) {
					update_sql = SQLUtils.construct_update_sql(table_name,
							columns, values, where_update, dbEntryKey);
				}

				if (!neverInsertTables.contains(table_name)) {
					insert_sql = SQLUtils.construct_insert_sql(table_name,
							columns, values, where_insert_cols,
							where_insert_vals, dbEntryKey);
				}
				executeSQL(update_sql, insert_sql);
			}
		}
	}

	/**
	 * Retrieves all information from the DB that matches the given key.
	 * 
	 * @param key
	 *            the primary key present in the tables to be used
	 * @return the answer collection list, one answer collection per table.
	 */
	public HashMap retrieveData(Key dbEntryKey) {
		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		HashMap map = new HashMap();
		AnswerCollection ac;
		Answer answer;

		try {
			Statement stm_tables = conn.createStatement();
			Statement tables_select = conn.createStatement();
			String current_table;
			String where = dbEntryKey.toString();

			// going through all the tables in the database.
			ResultSet tables = stm_tables.executeQuery("show tables");
			while (tables.next()) {
				current_table = tables.getString(1);

				String sql_no_id = "SELECT * FROM " + current_table + where;
				String sql = "SELECT * FROM " + current_table + where
						+ " ORDER BY id";

				// check if table contains an ID column
				try {
					tables_select.executeQuery(sql);
				} catch (Exception e) {
					// we got an exception, id column is missing
					sql = sql_no_id;
				}

				/*----------------------------------------------*/
				System.out.println("------------" + current_table
						+ "------------");
				/*----------------------------------------------*/

				// going through the information of current table.
				ac = new AnswerCollection();
				ResultSet data = tables_select.executeQuery(sql);
				while (data.next()) {
					answer = new Answer();

					// going through the columns and adding col-val to the
					// answer.
					ResultSetMetaData meta = data.getMetaData();
					for (int i = 1; i <= meta.getColumnCount(); i++) {

						// if we're dealing with a blob, we don't want to treat
						// it like other data (storing it as a String). We want
						// to parse the blob and get its name.
						int columnType = meta.getColumnType(i);
						if (!(columnType == Types.BINARY
								|| columnType == Types.VARBINARY
								|| columnType == Types.LONGVARBINARY || columnType == Types.BLOB)) {
							answer.add(meta.getColumnName(i), current_table,
									data.getString(i));

							/*----------------------------------------------*/
							System.out.println(meta.getColumnName(i) + " = "
									+ data.getString(i));
							/*----------------------------------------------*/
						} else {
							
						}
					}

					// adding the anser to the answer collection
					ac.add(answer);
				}

				// adding the answer collection to hashmap
				map.put(current_table, ac);
			}
		} catch (Exception e) {
			System.out.println(e.toString());
		}

		return map;
	}

	/**
	 * Given the username, resets the password and sends an email to the address
	 * associated with the username
	 * 
	 * @param username
	 *            the username of the person who's password is to be reset.
	 */
	public void resetPassword(String username) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm
					.executeQuery("SELECT * FROM users WHERE username='"
							+ username + "'");

			// if this user exists.
			if (rs.next()) {
				String email = rs.getString(3);
				String new_pass = "";
				for (int i = 0; i < 6; i++) {
					new_pass += (char) (Math.random() * 25 + 97);
				}

				stm.executeUpdate("UPDATE users SET password='" + new_pass
						+ "' WHERE username='" + username + "'");
				new PasswordResetEmail(email, username, new_pass);
			}
		} catch (Exception e) {

		}
	}

	/**
	 * Returns true if the provided username and password are in the database
	 * 
	 * @param user
	 *            the user id
	 * @param pass
	 *            the user password.
	 * @return true if the username and password exist in the database, false
	 *         otherwise.
	 */
	public User validateLogin(String user, String pass) {
		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		try {
			Statement valid_stm = conn.createStatement();
			ResultSet rs = valid_stm
					.executeQuery("SELECT * FROM users WHERE username='" + user
							+ "' AND password='" + pass + "'");
			if (rs.next()) {
				String permission = rs.getString(6);
				User user_obj = new User();
				user_obj.setUsername(user);
				user_obj.setPermission(permission);
				System.out.println("SERVER setting users permission to"
						+ permission);
				return user_obj;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * This will look up possible values depending on what is typed in so far.
	 */
	public ArrayList getSuggestions(String fieldName, String tableName,
			String so_far) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		ArrayList results = new ArrayList();
		String sql = "SELECT DISTINCT " + fieldName + " FROM " + tableName
				+ " WHERE " + fieldName + " LIKE '%" + so_far
				+ "%' LIMIT 0 , 30";

		System.out.println("SUGEST SQL: " + sql);

		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);

			while (rs.next()) {
				results.add(rs.getString(1));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return results;
	}

	/**
	 * Retreives the permission for a given user.
	 */
	public String getUserPermission(String username) {
		if (conn == null) {
			getConnection();
		}

		String permission = "";
		String sql = "SELECT permission FROM users WHERE username='" + username
				+ "'";

		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);

			while (rs.next()) {
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public boolean authenticate(String username, String password) {
		return ServerLDAPAuthentication.authenticate(username, password);
	}

	/**
	 * Stores a file into the database using the <code>filePath</code>.
	 * Returns true if the insertion/update statement executed correctly and
	 * false otherwise.
	 * 
	 * @param fieldName
	 *            the database field name where to store the file. This must be
	 *            a blob field.
	 * @param tableName
	 *            the name of the table where fieldName is.
	 * @param dbKey
	 *            the key associated with the file.
	 * @param filePath
	 *            the path <b>on the server</b> where the file lives.
	 * @return true if the insertion/update statement executed correctly and
	 *         false otherwise.
	 */
	public boolean storeFile(String fieldName, String tableName, Key dbKey,
			String filePath) {
		boolean result = false;

		String update_sql = SQLUtils.construct_update_sql(tableName, fieldName,
				"?", "", dbKey);

		String insert_sql = SQLUtils.construct_update_sql(tableName, fieldName,
				"?", "", dbKey);

		result = executeFileStoreSQL(update_sql, filePath);
		if (!result) {
			result = executeFileStoreSQL(insert_sql, filePath);
		}

		return result;
	}

	/**
	 * Executes the specified SQL by preparing a statement and inserting a
	 * {@link FileInputStream} into it using the file found at
	 * <code>filePath</code>. We are assuming that the SQL statement is
	 * almost complete aside from one question mark which will be filled with
	 * the {@link FileInputStream}. Returns true if there were any rows
	 * effected and false otherwise.
	 * 
	 * @param sql
	 *            the sql statement to prepare and attempt to execute.
	 * @param filePath
	 *            the path to the file we wish to store.
	 * @return true if there were any rows effected and false otherwise.
	 */
	private boolean executeFileStoreSQL(String sql, String filePath) {
		boolean result = false;

		filePath = filePath.trim();

		Statement stm;
		PreparedStatement pstm;
		File file;
		FileInputStream fileIS;
		int rows_affected = 0;

		try {
			stm = conn.createStatement();
			pstm = conn.prepareStatement(sql);

			// set the question mark to a file so we can store it as a blob.
			file = new File(filePath);
			fileIS = new FileInputStream(file);
			pstm.setBinaryStream(1, fileIS, (int) (file.length()));

			// execute the statement
			rows_affected = pstm.executeUpdate();

			if (rows_affected > 0) {
				had_an_update = true;
				result = true;
				System.out.println("SUCCESSFULLY EXECUTED: " + sql);
			}
			stm.close();
		} catch (Exception e) {
			System.out.println("FAILED TO EXECUTE: " + sql);
			System.out.println(e.toString());
		}

		return result;
	}
}
